"""
Case Type   : Compatibility
Case Name   : 验证二级分区表中的bool值输出
Create At   : 2024/10/18
Owner       : liu-tong-8848
Description :
              1.创建hash-list分区表
              2.insert数据
              3.查询表数据
              4.创建list-range分区表
              5.分区表insert数据
              6.查询表数据
              7.清理环境
Expect      :
              1.成功
              2.成功
              3.成功
              4.成功
              5.成功
              6.成功
              7.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.db_user_node = Node(node='PrimaryDbBmode')

    def test_add_dimension(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        step1 = '--step1：创建hash-list分区表 expect：建表成功'
        self.log.info(step1)
        sql = "drop table if exists tab_bool_0004_01; create table tab_bool_0004_01(a bool,b int,c bool,d int)" \
              + "partition by hash(b) subpartition by list(d)( partition p1( subpartition sp1 values(10), " \
              + "subpartition sp2 values(20) ), partition p2);"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step1)

        step2 = '--step2：insert数据 expect：成功'
        self.log.info(step2)
        sql = " insert into tab_bool_0004_01 values (true,12,false,10),(0,4,1,20)," \
              + "(false,9,1,20), (true,1,0,10),(0,14,false,10);"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result, "执行失败" + step2)

        step3 = '--step3：查询数据 expect：成功'
        self.log.info(step3)
        sql = "select * from tab_bool_0004_01;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("t | 12 | f | 10", result, "执行失败" + step3)
        self.assertIn("f |  4 | t | 20", result, "执行失败" + step3)
        self.assertIn("f |  9 | t | 20", result, "执行失败" + step3)
        self.assertIn("t |  1 | f | 10", result, "执行失败" + step3)
        self.assertIn("f | 14 | f | 10", result, "执行失败" + step3)

        step4 = '--step4：创建list-range分区表 expect：建表成功'
        self.log.info(step4)
        sql = "drop table if exists tab_bool_0004_02; create table tab_bool_0004_02 (a bool ,b int ," \
              + "c bool ,d int) partition by list(b) subpartition by range(d)( partition p1 values(5)( subpartition " \
              + "sp1 values less than(10), subpartition sp2 values less than(20) ), partition p2 values(10) ); "
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step4)

        step5 = '--step5：分区表insert数据 expect：成功'
        self.log.info(step5)
        sql = "insert into tab_bool_0004_02 values (true,10,false,7),(0,5,1,12),(false,10,1,11), " \
              + "(true,10,0,15),(0,5,false,3); "
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result, "执行失败" + step5)

        step6 = '--step6：查询表数据 expect：成功'
        self.log.info(step6)
        sql = "select * from tab_bool_0004_02;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("t | 10 | f |  7", result, "执行失败" + step6)
        self.assertIn("f |  5 | t | 12", result, "执行失败" + step6)
        self.assertIn("f | 10 | t | 11", result, "执行失败" + step6)
        self.assertIn("t | 10 | f | 15", result, "执行失败" + step6)
        self.assertIn("f |  5 | f |  3", result, "执行失败" + step6)

    def tearDown(self):
        step7 = 'step7:清理环境 expect:清理环境成功'
        self.log.info(step7)
        clean_sql = "drop table if exists tab_bool_0004_01; drop table if exists tab_bool_0004_02;"
        clean_result = self.primary_sh.execut_db_sql(clean_sql)
        self.assertIn("DROP", clean_result, "执行失败" + step7)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
